package suite;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import suite.exception.SuiteInvalidaException;
import util.Conexao;
import util.ErroAcessoRepositorioException;

public class RepositorioSuitesBDR implements IRepositorioSuites {

	private static final String INSERT_SUITE = "INSERT INTO suite (ramal," +
			"status_suite,preco_hora_excedente,preco,tp_suite) VALUES (?,?,?,?,?)";
	
	private static final String DELETE_SUITE = "DELETE FROM suite WHERE id_suite = ?";
	
	private static final String MSG_ERRO_ACESSO_BD = "Erro de acesso ao banco de dados";

	private static final String CONSULTA_EXISTE = "SELECT * FROM suite WHERE id_suite = ?";
	
	private static final String UPDATE_SUITE = "UPDATE suite SET ramal = ?, status_suite = ?, preco_hora_excedente = ?," +
			"preco = ?, tp_suite = ? WHERE id_suite = ?";
	
	private static final String CONSULTA_LISTAR = "SELECT * FROM suite";
	
	
	private void levantaErroAcessoException(SQLException e) throws ErroAcessoRepositorioException {
		throw new ErroAcessoRepositorioException(MSG_ERRO_ACESSO_BD,
				e,
				e.getErrorCode());
	}

	public void atualizarSuite(SuiteAbstrata suite)
			throws SuiteInvalidaException, ErroAcessoRepositorioException {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = Conexao.getConexao();
			pstmt = con.prepareStatement(UPDATE_SUITE);
			pstmt.setString(1, suite.getRamal());
			pstmt.setInt(2, suite.getStatusSuite());
			pstmt.setDouble(3, suite.getPrecoHoraExcedente());
			pstmt.setDouble(4, suite.getPreco());
			if (suite instanceof SuiteStandard){
				pstmt.setInt(5, 1);
			}else if(suite instanceof SuiteLuxo){
				pstmt.setInt(5, 2);
			}else if(suite instanceof SuiteSuperLuxo){
				pstmt.setInt(5, 3);
			}else{
				pstmt.setInt(5, 4);
			}
			pstmt.setInt(6, suite.getNumeroSuite());
			int rows = pstmt.executeUpdate();
			if (rows == 0){
				throw new SuiteInvalidaException(suite.getNumeroSuite());
			}
		} catch (SQLException e) {
			levantaErroAcessoException(e);
		} finally{
				Conexao.fechaRecursos(con, pstmt, null);
		}

	}


	
	public SuiteAbstrata consultarSuite(int numeroSuite)
			throws SuiteInvalidaException, ErroAcessoRepositorioException {
		SuiteAbstrata suite = null;
		PreparedStatement pstmt = null;
		Connection con = null;
		ResultSet rs = null;
		try {
			con = Conexao.getConexao();
			pstmt = con.prepareStatement(CONSULTA_EXISTE);
			pstmt.setInt(1, numeroSuite);
			rs = pstmt.executeQuery();
			if(rs.next()){
				int id = rs.getInt("id_suite");
				String ramal = rs.getString("ramal");
				int statusSuite = rs.getInt("status_suite");
				double precoExcedente = rs.getDouble("preco_hora_excedente");
				double preco = rs.getDouble("preco");
				int tipoSuite = rs.getInt("tp_suite");
				if (tipoSuite == 1){
					suite = new SuiteStandard(id,ramal,statusSuite,precoExcedente,preco);
				}else if (tipoSuite == 2){
					suite = new SuiteLuxo(id,ramal,statusSuite,precoExcedente,preco);
				}else if (tipoSuite == 3){
					suite = new SuiteSuperLuxo(id,ramal,statusSuite,precoExcedente,preco);
				}else if (tipoSuite == 4){
					suite = new SuitePresidencial(id,ramal,statusSuite,precoExcedente,preco);
				}
			}else{
				throw new SuiteInvalidaException(numeroSuite);
			}
		} catch (SQLException e) {
			levantaErroAcessoException(e);
		} finally{
				Conexao.fechaRecursos(con, pstmt, null);
		}
		return suite;
	}

	
	public void excluirSuite(int numeroSuite) throws SuiteInvalidaException, ErroAcessoRepositorioException {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = Conexao.getConexao();
			pstmt = con.prepareStatement(DELETE_SUITE);
			pstmt.setInt(1, numeroSuite);
			int rows = pstmt.executeUpdate();
			if (rows == 0){
				throw new SuiteInvalidaException(numeroSuite);
			}
		} catch (SQLException e) {
			levantaErroAcessoException(e);
		} finally{
				Conexao.fechaRecursos(con, pstmt, null);
		}

	}

	public void inserirSuite(SuiteAbstrata suite) throws ErroAcessoRepositorioException {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = Conexao.getConexao();
			pstmt = con.prepareStatement(INSERT_SUITE);
			pstmt.setString(1, suite.getRamal());
			pstmt.setInt(2, 1);
			pstmt.setDouble(3, suite.getPrecoHoraExcedente());
			pstmt.setDouble(4, suite.getPreco());
			if (suite instanceof SuiteStandard){
				pstmt.setInt(5, 1);
			}else if (suite instanceof SuiteLuxo){
				pstmt.setInt(5, 2);
			}else if (suite instanceof SuiteSuperLuxo){
				pstmt.setInt(5, 3);
			}else{
				pstmt.setInt(5, 4);
			}
			pstmt.executeUpdate();
		} catch (SQLException e) {
			levantaErroAcessoException(e);
		} finally{
			Conexao.fechaRecursos(con, pstmt, null);
		}
	}

	
	public List<SuiteAbstrata> listarSuites() throws ErroAcessoRepositorioException {
		PreparedStatement pstmt = null;
		Connection con = null;
		ResultSet rs = null;
		List<SuiteAbstrata> suites = new ArrayList<SuiteAbstrata>();
		SuiteAbstrata suite = null;
		try {
			con = Conexao.getConexao();
			pstmt = con.prepareStatement(CONSULTA_LISTAR);
			rs = pstmt.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id_suite");
				String ramal = rs.getString("ramal");
				int status = rs.getInt("status_suite");
				double precoHoraExcedente = rs.getDouble("preco_hora_excedente");
				double preco = rs.getDouble("preco");
				int tipoSuite = rs.getInt("tp_suite");
				if(tipoSuite==1){
					suite = new SuiteStandard(id,ramal,status,precoHoraExcedente,preco);
				}else if(tipoSuite == 2){
					suite = new SuiteLuxo(id,ramal,status,precoHoraExcedente,preco);
				}else if(tipoSuite == 3){
					suite = new SuiteSuperLuxo(id,ramal,status,precoHoraExcedente,preco);
				}else {
					suite = new SuitePresidencial(id,ramal,status,precoHoraExcedente,preco);
				}
				suites.add(suite);
			}
		} catch (SQLException e) {
			levantaErroAcessoException(e);
		} finally{
			Conexao.fechaRecursos(con, pstmt, null);
		}
		return suites;
	}

}
